package com.yhp.dao.impl;

import com.yhp.bean.Courier;
import com.yhp.bean.User;
import com.yhp.dao.BaseUser;
import com.yhp.util.Dbutils;

import javax.jws.soap.SOAPBinding;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class UserDaoImpl extends Dbutils implements BaseUser {

    //查询全部快递员
    private static final String SQL_FIND_ALL = "SELECT * FROM USER";
    //分页查询全部快递员
    private static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    //查找总条数
    private static final String SQL_TOTAL = "SELECT COUNT(*) FROM USER";
    //快递员添加
    private static final String SQL_INSERT = "INSERT INTO USER(NAME,PHONE,NUMBERID,PASSWORD,INTIME) VALUES(?,?,?,?,NOW())";
    //根据手机号查找快递员信息
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM USER WHERE PHONE = ?";
    //快递员修改
    private static final String SQL_UPDATE = "UPDATE USER SET NAME=?,PHONE=?,NUMBERID=?,PASSWORD=? WHERE ID=?";
    //快递员删除
    private static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";
    //根据手机号查找用户信息
    private static final String SQL_FIND_BY_NAME = "SELECT * FROM USER WHERE NAME = ?";
    //修改登陆时间
    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE USER SET TIME=? WHERE NAME=?";

    /**
     * 用于查询所有用户
     *
     * @param limit      是否分页  true表示分页  false表示查询全部
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        ResultSet result = null;
        List<User> list = new ArrayList();
        try {
            if (limit) {
                stats = connection.prepareStatement(SQL_FIND_ALL_LIMIT);
                stats.setInt(1, offset);
                stats.setInt(2, pageNumber);
            } else {
                stats = connection.prepareStatement(SQL_FIND_ALL);
            }
            result = stats.executeQuery();
            while (result.next()) {
                User user = new User();
                user.setId(result.getInt("id"));
                user.setName(result.getString("name"));
                user.setPhone(result.getString("phone"));
                user.setPassWord(result.getString("password"));
                user.setInTime(result.getTimestamp("intime"));
                user.setTime(result.getTimestamp("time"));
                user.setNumberId(result.getString("numberid"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /***
     *  查找总条数
     * @return 返回总条数
     */
    @Override
    public int total() {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        ResultSet result = null;
        int i = 0;
        try {
            stats = connection.prepareStatement(SQL_TOTAL);
            result = stats.executeQuery();
            while (result.next()){
                i=result.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /***
     * 用户的添加
     * @param e  录入的对象
     * @return 返回  录入成功或者失败
     */
    @Override
    public boolean insert(User e) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        int i = 0;
        try {
            stats = connection.prepareStatement(SQL_INSERT);
            stats.setString(1, e.getName());
            stats.setString(2, e.getPhone());
            stats.setString(3, e.getNumberId());
            stats.setString(4, e.getPassWord());
            i = stats.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return i>0;
    }

    /**
     * 用户的修改
     *
     * @param id   要修改的快递员的id
     * @param newe 新的快递员对象  修改的数据
     * @return 返回成功或者失败
     */
    @Override
    public boolean update(int id, User newe) {
        return false;
    }

    /***
     * 删除用户
     * @param id  要删除快递员的id
     * @return 返回成功或者失败
     */
    @Override
    public boolean delete(int id) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        int i = 0;
        try {
            stats = connection.prepareStatement(SQL_DELETE);
            stats.setInt(1,id);
            i = stats.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
        return i>0;

    }

    /***
     * 根据手机号查找用户信息
     * @param phone   快递员手机号
     * @return 返回快递员信息
     */
    @Override
    public User findByPhone(String phone) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        ResultSet result = null;
        try {
            stats = connection.prepareStatement(SQL_FIND_BY_PHONE);
            stats.setString(1,phone);
            result = stats.executeQuery();
            User u = new User();
            while (result.next()){
                u.setId(result.getInt("id"));
                u.setName(result.getString("name"));
                u.setNumberId(result.getString("numberid"));
                u.setPhone(result.getString("phone"));
                u.setPassWord(result.getString("password"));
                u.setInTime(result.getTimestamp("intime"));
                u.setTime(result.getTimestamp("time"));
                return u;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
        return null;
    }

    /***
     * 根据手机号查找用户信息
     * @param name
     * @return
     */
    @Override
    public User findByName(String name) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        ResultSet result = null;
        try {
            stats = connection.prepareStatement(SQL_FIND_BY_NAME);
            stats.setString(1,name);
            result = stats.executeQuery();
            User u = new User();
            while (result.next()){
                u.setId(result.getInt("id"));
                u.setName(result.getString("name"));
                u.setNumberId(result.getString("numberid"));
                u.setPhone(result.getString("phone"));
                u.setPassWord(result.getString("password"));
                u.setInTime(result.getTimestamp("intime"));
                u.setTime(result.getTimestamp("time"));
                return u;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }

        return null;
    }

    /**
     * 3
     * 修改登陆时间
     *
     * @param date
     * @param name
     */
    @Override
    public void updateTime(Date date, String name) {
        Connection connection = Dbutils.getConnection();
        PreparedStatement stats = null;
        int i = 0;
        try {
            stats = connection.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            stats.setDate(1,new java.sql.Date(date.getTime()));
            stats.setString(2,name);
            i = stats.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
    }

}
